from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, JSON, String, TIMESTAMP, text
from sqlalchemy.dialects.mysql import INTEGER, TEXT, VARCHAR

Base = declarative_base()
metadata = Base.metadata


class Task(Base):
    __tablename__ = 'task'
    id = Column(INTEGER(11), primary_key=True)
    dispatch_code = Column(VARCHAR(50), comment='调度编码，每个调度中心下的服务唯一')
    task_id = Column(INTEGER(11), comment='任务id')
    task_name = Column(VARCHAR(255), comment='任务名')
    task_frequency = Column(VARCHAR(50), comment='执行频率：hourly/daily/weekly/monthly')
    task_status = Column(VARCHAR(50), comment='任务启用状态: on/off')
    sql_id = Column(INTEGER(11), comment='sql id')
    execute_database_id = Column(INTEGER(11), comment='数据库id')
    receive_service_id = Column(JSON, comment='接收数据的服务id')
    storage_database_id = Column(JSON, comment='数据库id')
    storage_table_id = Column(JSON, comment='表id')
    comment = Column(VARCHAR(100), comment='备注')
    create_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
    delete_time = Column(TIMESTAMP)

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class SQL(Base):
    __tablename__ = 'sql'
    id = Column(INTEGER(11), primary_key=True)
    dispatch_code = Column(VARCHAR(50), comment='调度编码，每个调度中心下的服务唯一')
    sql_id = Column(INTEGER(11), comment='sql id')
    sql_type = Column(VARCHAR(50), comment='sql类型：sql/procedure')
    sql_template = Column(TEXT, comment='sql/procedure 模板')
    sql_variable = Column(JSON, comment='sql中需要替换的参数')
    comment = Column(VARCHAR(255), comment='备注')
    create_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
    delete_time = Column(TIMESTAMP)

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Database(Base):
    __tablename__ = 'database'
    id = Column(INTEGER(11), primary_key=True)
    dispatch_code = Column(VARCHAR(50), comment='调度编码，每个调度中心下的服务唯一')
    database_id = Column(INTEGER(11), comment='数据库id')
    database_type = Column(VARCHAR(50), comment='数据库类型：mysql/oracle/sqlServer')
    database_name = Column(VARCHAR(50), comment='数据库名称')
    database_host = Column(VARCHAR(50), comment='域名/ip')
    database_port = Column(INTEGER(11), comment='端口号')
    database_username = Column(VARCHAR(50), comment='登录用户')
    database_password = Column(VARCHAR(50), comment='登录密码')
    database_extra_parameter = Column(VARCHAR(100), comment='连接url额外参数')
    database_driver = Column(String(50), comment='指定驱动')
    comment = Column(VARCHAR(100), comment='备注')
    create_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
    delete_time = Column(TIMESTAMP)

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Table(Base):
    __tablename__ = 'table'
    id = Column(INTEGER(11), primary_key=True)
    dispatch_code = Column(VARCHAR(50), comment='调度编码，每个调度中心下的服务唯一')
    table_id = Column(INTEGER(11), comment='表id')
    database_id = Column(INTEGER(11), comment='数据库id')
    table_name = Column(VARCHAR(50), comment='表名')
    comment = Column(VARCHAR(100), comment='备注')
    create_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
    delete_time = Column(TIMESTAMP)

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Job(Base):
    __tablename__ = 'job'
    id = Column(INTEGER(11), primary_key=True)
    dispatch_code = Column(VARCHAR(50), comment='调度编码，每个调度中心下的服务唯一')
    job_id = Column(INTEGER(11), comment='job id')
    job_name = Column(VARCHAR(50), comment='任务名')
    job_type = Column(VARCHAR(50), comment='调度式/链路式')
    job_frequency = Column(VARCHAR(50), comment='执行频率：hourly/daily/weekly/monthly')
    job_status = Column(VARCHAR(50), comment='job启用状态: on/off')
    task_id = Column(INTEGER(11), comment='任务id')
    last_job_id = Column(JSON, comment='上一次调度的job id')
    next_job_id = Column(JSON, comment='下一次调度的job id')
    comment = Column(VARCHAR(100), comment='备注')
    create_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
    delete_time = Column(TIMESTAMP)

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Service(Base):
    __tablename__ = 'service'
    id = Column(INTEGER(11), primary_key=True)
    center_dispatch_code = Column(VARCHAR(50), comment='调度中心的调度编码(用来形容是否属于同一个调度管理圈)')
    dispatch_code = Column(VARCHAR(50), comment='调度编码，每个调度中心下的服务唯一')
    service_level = Column(INTEGER(11), comment='服务级别: 1为该服务与本服务在该中心下为平级, 2为该服务为本服务的直属调度中心, 3为该服务为该中心的父级中心')
    service_id = Column(INTEGER(11), comment='服务id')
    service_name = Column(VARCHAR(50), comment='服务名')
    service_host = Column(VARCHAR(50), comment='域名/ip')
    service_port = Column(INTEGER(11), comment='端口号')
    service_type = Column(VARCHAR(50), comment='服务类型：http/rpc')
    service_url = Column(VARCHAR(100), comment='服务url')
    comment = Column(VARCHAR(100), comment='备注')
    create_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
    delete_time = Column(TIMESTAMP)

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}
